﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Maticsoft.DBUtility;

namespace EnterpriseFund.DAL
{
    public class Common
    {

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="fOrderFiled"></param>
        /// <returns></returns>
        public DataSet GetListByPage(string table, string fields, int PageSize, int PageIndex, string strWhere, string fOrderFiled, out int total)
        {

            SqlParameter[] parameters = {
                    new SqlParameter("@tbname", SqlDbType.VarChar, 255),
                    new SqlParameter("@FieldKey", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageCurrent", SqlDbType.Int),
                    new SqlParameter("@FieldShow", SqlDbType.VarChar,255),
                    new SqlParameter("@FieldOrder", SqlDbType.VarChar,255),
                    new SqlParameter("@Where", SqlDbType.VarChar,1000),
                    new SqlParameter("@PageCount",SqlDbType.Int)
                    };
            parameters[0].Value = table;
            parameters[1].Value = fields;
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = "";
            parameters[5].Value = fOrderFiled;
            parameters[6].Value = strWhere;
            parameters[7].Direction = ParameterDirection.Output;
            DataSet ds = DbHelperSQL.RunProcedure("sp_PageView", parameters, "ds", 60 * 30);
            total = int.Parse(parameters[7].Value.ToString());
            return ds;
        }

        /// <summary>
        /// 获取比较数据（sql）
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataSet GetCompareDataBySQL(string tableName,string colunms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select " + colunms + " from " + tableName);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        ///获取年度
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataSet GetYear()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT DISTINCT FYear FROM fund.t_TaxClassAllowance");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 年度反结转
        /// <creator>zhongyl</creator>
        /// <date>2014-11-07</date>
        /// </summary>
        /// <param name="year"></param>
        /// <param name="FStatus"></param>
        /// <returns></returns>
        public void YearTurnChange(decimal year)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            strSql.Append("delete from fund.t_EnterpriseFinanceProp where FYear=@FYear ;");
            strSql.Append("delete from fund.t_RejectTax where FYear=@FYear ;");
            strSql.Append("delete from fund.t_TaxClassAllowance where FYear=@FYear ;");
            strSql.Append("delete from fund.t_SpecialAdjustCls where FYear=@FYear ;");
            strSql.Append("delete from fund.t_DJ_NSRXX_Extension1 where FYear=@FYear ;");
            strSql.Append("delete from fund.t_SupportPolicy where FYear=@FYear ;");
            SqlParameter[] parameters = {
                    new SqlParameter("@FYear", SqlDbType.Decimal, 4)
                                         };
            parameters[0].Value = year;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);

        }

        public void YearTransfer(string year, List<Model.SupportPolicy> sl)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();

            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append(@"INSERT INTO fund.t_EnterpriseFinanceProp (FFinancePropNo,FYear,FEnterprisePropNo)
                            SELECT   FFinancePropNo ,FYear+1 ,FEnterprisePropNo FROM fund.t_EnterpriseFinanceProp WHERE FYear =@FYear");
            SqlParameter[] parameters6 = {
					new SqlParameter("@FYear", SqlDbType.Decimal,6)
					};
            parameters6[0].Value = year;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters6;
            cmdList.Add(cmds);

            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append(@"INSERT INTO fund.t_RejectTax (FRejectTaxNo ,
        FYear ,
        FRejectEnterpriseNote ,
        FRejectTaxNote ,
        FRejectEnterpriseSQL ,
        FRejectTaxSQL)
SELECT   FRejectTaxNo ,
        FYear+1 AS FYear ,
        FRejectEnterpriseNote ,
        FRejectTaxNote ,
        FRejectEnterpriseSQL ,
        FRejectTaxSQL FROM fund.t_RejectTax WHERE FYear =@FYear
");
            SqlParameter[] parameters2 = {
					new SqlParameter("@FYear", SqlDbType.Decimal,6)
					};
            parameters2[0].Value = year;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);

            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append(@"INSERT INTO fund.t_TaxClassAllowance (FYear ,
        FTaxClassAllowanceNo ,
        FTaxClassNo ,
        FTaxClassAllowanceName ,
        FSQLCondition ,
        FSeq)
SELECT   FYear+1 AS FYear ,
        FTaxClassAllowanceNo ,
        FTaxClassNo ,
        FTaxClassAllowanceName ,
        FSQLCondition ,
        FSeq FROM fund.t_TaxClassAllowance WHERE FYear =@FYear
");
            SqlParameter[] parameters3 = {
					new SqlParameter("@FYear", SqlDbType.Decimal,6)
					};
            parameters3[0].Value = year;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters3;
            cmdList.Add(cmds);


            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append(@"INSERT INTO fund.t_SpecialAdjustCls (FSpecialAdjustClsNo ,
        FYear ,
        FSpecialAdjustName)
SELECT   FSpecialAdjustClsNo ,
        FYear+1 AS FYear ,
        FSpecialAdjustName FROM fund.t_SpecialAdjustCls WHERE FYear =@FYear

");
            SqlParameter[] parameters4= {
					new SqlParameter("@FYear", SqlDbType.Decimal,6)
                                        };
            parameters4[0].Value = year;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters4;
            cmdList.Add(cmds);

            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append(@"INSERT INTO fund.t_DJ_NSRXX_Extension1 (NSRDZDAH ,
        FFinancePropNo ,
        FYear ,
        FEnterprisePropNo ,
        FEntityNo ,
        FInsEmpNo ,
        FInsDeptNo ,
        FInsDate ,
        FInsIP ,
        FUpdEmplNo ,
        FUpdDeptNo ,
        FUpdDate ,
        FUpdIP)
SELECT   NSRDZDAH ,
        FFinancePropNo ,
        FYear+1 AS FYear ,
        FEnterprisePropNo ,
        FEntityNo ,
        FInsEmpNo ,
        FInsDeptNo ,
        FInsDate ,
        FInsIP ,
        FUpdEmplNo ,
        FUpdDeptNo ,
        FUpdDate ,
        FUpdIP FROM fund.t_DJ_NSRXX_Extension1 WHERE FYear =@FYear

");
            SqlParameter[] parameters5 = {
					new SqlParameter("@FYear", SqlDbType.Decimal,6)
                                        };
            parameters5[0].Value = year;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters5;
            cmdList.Add(cmds);

            foreach (Model.SupportPolicy model in sl)
            {
                var getBillIdSql = "exec sp_getbillno 'fund.t_SupportPolicy','FPolicyID','','','',@returnBId output ";
                var getBillIdPar = new SqlParameter[]{ 
                new SqlParameter("@returnBId", SqlDbType.VarChar, 50)
            };
                getBillIdPar[0].Direction = ParameterDirection.Output;


                strSql = new StringBuilder();
                strSql.Append("insert into fund.t_SupportPolicy(");
                strSql.Append("FPolicyID,FYear,FEnterprisePropNo,FTaxClassAllowanceNo,FRuleDesc,FRuleFormula,FRuleExpression,FIsEvalBase,FInnerRate,FOuterRate,FComputeDesc,FComputeFormula,FComputeExpression,FNote,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP,FProp,FPolicyName)");
                strSql.Append(" values (");
                strSql.Append("@FPolicyID,@FYear,@FEnterprisePropNo,@FTaxClassAllowanceNo,@FRuleDesc,@FRuleFormula,@FRuleExpression,@FIsEvalBase,@FInnerRate,@FOuterRate,@FComputeDesc,@FComputeFormula,@FComputeExpression,@FNote,@FInsEmpNo,@FInsDeptNo,@FInsDate,@FInsIP,@FUpdEmplNo,@FUpdDeptNo,@FUpdDate,@FUpdIP,@FProp,@FPolicyName)");
                SqlParameter[] parameters = {
					new SqlParameter("@FPolicyID", SqlDbType.NVarChar,50),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FEnterprisePropNo", SqlDbType.NVarChar,6),
					new SqlParameter("@FTaxClassAllowanceNo", SqlDbType.NVarChar,20),
					new SqlParameter("@FRuleDesc", SqlDbType.NVarChar,200),
					new SqlParameter("@FRuleFormula", SqlDbType.NVarChar,-1),
					new SqlParameter("@FRuleExpression", SqlDbType.NVarChar,-1),
					new SqlParameter("@FIsEvalBase", SqlDbType.NVarChar,1),
					new SqlParameter("@FInnerRate", SqlDbType.Decimal,5),
					new SqlParameter("@FOuterRate", SqlDbType.Decimal,5),
					new SqlParameter("@FComputeDesc", SqlDbType.NVarChar,200),
					new SqlParameter("@FComputeFormula", SqlDbType.NVarChar,-1),
					new SqlParameter("@FComputeExpression", SqlDbType.NVarChar,-1),
					new SqlParameter("@FNote", SqlDbType.NVarChar,-1),
					new SqlParameter("@FInsEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FInsDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime,30),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
					new SqlParameter("@FProp", SqlDbType.NVarChar,1),
					new SqlParameter("@FPolicyName", SqlDbType.NVarChar,50)};
                parameters[0].Value = model.FPolicyID;
                parameters[0].Direction = ParameterDirection.InputOutput;
                parameters[1].Value = model.FYear;
                parameters[2].Value = model.FEnterprisePropNo;
                parameters[3].Value = model.FTaxClassAllowanceNo;
                parameters[4].Value = model.FRuleDesc;
                parameters[5].Value = model.FRuleFormula;
                parameters[6].Value = model.FRuleExpression;
                parameters[7].Value = model.FIsEvalBase;
                parameters[8].Value = model.FInnerRate;
                parameters[9].Value = model.FOuterRate;
                parameters[10].Value = model.FComputeDesc;
                parameters[11].Value = model.FComputeFormula;
                parameters[12].Value = model.FComputeExpression;
                parameters[13].Value = model.FNote;
                parameters[14].Value = model.FInsEmpNo;
                parameters[15].Value = model.FInsDeptNo;
                parameters[16].Value = model.FInsDate;
                parameters[17].Value = model.FInsIP;
                parameters[18].Value = model.FUpdEmplNo;
                parameters[19].Value = model.FUpdDeptNo;
                parameters[20].Value = model.FUpdDate;
                parameters[21].Value = model.FUpdIP;
                parameters[22].Value = model.FProp;
                parameters[23].Value = model.FPolicyName;

                cmdList.Add(new CommandInfo(getBillIdSql, getBillIdPar));
                cmdList.Add(new CommandInfo(strSql.ToString(), parameters));
            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);
        }

        /// <summary>
        /// 更新比较数据（sql）
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public void UpdateCompareData(string key,string tableName, string colunms, DataRow[] addDr, DataRow[] EditDr)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            List<SqlParameter> SqlParameterList = new List<SqlParameter>();
            string[] colunmsstr = colunms.Split(',');

            strSql.Append("insert into " + tableName + "(");
            strSql.Append(colunms + ")");
            strSql.Append(" values (");
            int i = 0;
            foreach (string str in colunmsstr)
            {
                if (i == 0)
                {
                    strSql.Append("@" + str);
                }
                else
                {
                    strSql.Append(",@" + str);
                }
                i++;
            }
            strSql.Append(")");

            foreach (DataRow dr in addDr)
            {
                SqlParameterList = new List<SqlParameter>();
                cmds = new CommandInfo();
                for (int z = 0; z < colunmsstr.Length; z++)
                {
                    SqlParameterList.Add(new SqlParameter("@" + colunmsstr[z], dr[colunmsstr[z]]));
                }

                cmds.Parameters = SqlParameterList.ToArray();
                cmds.CommandText = strSql.ToString();
                cmdList.Add(cmds);
            }

            strSql = new StringBuilder();
            strSql.Append("update  " + tableName + " set ");
            i = 0;
            foreach (string str in colunmsstr)
            {
                if (str != key)
                {
                    if (i == 0)
                    {
                        strSql.Append(str + "=@" + str);
                    }
                    else
                    {
                        strSql.Append("," + str + "=@" + str);
                    }
                    i++;
                }
            }
            strSql.Append(" where " + key + "=@" + key);

            foreach (DataRow dr in EditDr)
            {
                SqlParameterList = new List<SqlParameter>();
                cmds = new CommandInfo();
                for (int z = 0; z < colunmsstr.Length; z++)
                {
                    SqlParameterList.Add(new SqlParameter("@" + colunmsstr[z], dr[colunmsstr[z]]));
                }

                cmds.Parameters = SqlParameterList.ToArray();
                cmds.CommandText = strSql.ToString();
                cmdList.Add(cmds);
            }


            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList);
            }
        }


        /// <summary>
        /// 获取比较数据（ora）
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataSet GetCompareDataByOra(string tableName, string colunms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select " + colunms + " from " + tableName);
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取剔除数据
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public string GetGetRejectTaxSqlByYear(string year)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" SELECT fund.fn_GetRejectTaxSqlByYear(" + year + ")");
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="fOrderFiled"></param>
        /// <returns></returns>
        public DataSet GetListByPage2(string table, string fields, int PageSize, int PageIndex, string strWhere, string fOrderFiled, out int total)
        {

            SqlParameter[] parameters = {
                    new SqlParameter("@tbname", SqlDbType.VarChar, 255),
                    new SqlParameter("@FieldKey", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageCurrent", SqlDbType.Int),
                    new SqlParameter("@FieldShow", SqlDbType.VarChar,255),
                    new SqlParameter("@Where", SqlDbType.VarChar,1000),
                    new SqlParameter("@PageCount",SqlDbType.Int)
                    };
            parameters[0].Value = table;
            parameters[1].Value = fields;
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = "";
            parameters[5].Value = strWhere;
            parameters[6].Direction = ParameterDirection.Output;
            DataSet ds = DbHelperSQL.RunProcedure("sp_PageView", parameters, "ds", 60 * 30);
            total = int.Parse(parameters[6].Value.ToString());
            return ds;
        }
    }
}
